﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data.SqlClient;
using System.Data;
 
using System.Data.Common;

namespace DataProvider
{
    public class MySqlExecBlock<T> : IDisposable where T : BaseEntity, new()
    {
        private MySqlClient webClient = null;
        private SqlBlock sqlBlock;

        private Dictionary<String, String> sqlRepPara = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);


        public Dictionary<String, String> SqlRepPara
        {
            get { return sqlRepPara; }
        }

        private Dictionary<String, Object> sqlParaDict = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);

        public Dictionary<String, Object> SqlParaDict
        {
            get { return sqlParaDict; }
        }

        private Dictionary<String, SqlDbType> sqlOutParaDict = new Dictionary<string, SqlDbType>(StringComparer.OrdinalIgnoreCase);

        public Dictionary<String, SqlDbType> SqlOutParaDict
        {
            get { return sqlOutParaDict; }
        }
        private Dictionary<String, Object> outputParaDict = new Dictionary<string, Object>(StringComparer.OrdinalIgnoreCase);


        public Dictionary<String, Object> OutputParaDict
        {
            get { return outputParaDict; }
        }

        private List<String> filterFields = new List<string>();
        public List<String> FilterFields
        {
            get { return filterFields; }
        }

        public List<DbParameter> Parameters
        {
            set
            {
                if (this.sqlBlock != null && value != null)
                {
                    this.sqlBlock.Parameters.AddRange(value.ToArray());
                }
                else
                {
                    this.sqlBlock.Parameters.Clear();
                }
            }
            get
            {
                return this.sqlBlock.Parameters;
            }

        }

        ///////////////////////////////////////////////////////////////////
        public MySqlExecBlock(String sqlId, MySqlClient webClient)
        {
            this.sqlBlock = SqlConfig.GetSqlBlock(sqlId);
            this.webClient = webClient;
        }

        public MySqlExecBlock(String sql, SqlType sqlType, MySqlClient webClient)
        {
            this.sqlBlock = new SqlBlock();
            this.sqlBlock.Sql = sql;
            this.sqlBlock.SqlType = sqlType;
            this.webClient = webClient;
        }

        public MySqlExecBlock(SqlBlock sqlBlock, MySqlClient webClient)
        {
            this.sqlBlock = sqlBlock;
            this.webClient = webClient;
        }

        public MySqlExecBlock(String sqlId)
        {
            this.webClient = new MySqlClient(Config.ConnectionString);
            this.sqlBlock = SqlConfig.GetSqlBlock(sqlId);
        }

        public MySqlExecBlock(String sql, SqlType sqlType)
        {
            this.sqlBlock = new SqlBlock();
            this.sqlBlock.Sql = sql;
            this.sqlBlock.SqlType = sqlType;
            this.webClient = new MySqlClient(Config.ConnectionString);

        }

        public MySqlExecBlock(SqlBlock sqlBlock)
        {
            this.sqlBlock = sqlBlock;
            this.webClient = new MySqlClient(Config.ConnectionString);
        }

        ///////////////////////////////////////////////////////////////////
        public void ExecuteNoResult()
        {
            this.GenerateSqlParameter();
            if (this.sqlBlock.SqlType == SqlType.Text)
            {
                SqlStatemate state = new SqlStatemate(this.sqlBlock.Sql);
                if (this.sqlBlock.Parameters != null)
                {
                    state.Parameters.AddRange(this.sqlBlock.Parameters.ToArray());
                }
                this.webClient.ExecuteNoResult(state);
            }
            else
            {
                this.webClient.ExecuteStoreProcedureNoQuery(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
            }
        }

        public T ExecuteReturnEntity(params String[] selectFields)
        {
            this.GenerateSqlParameter();
            DbDataReader reader = null;
            if (this.sqlBlock.SqlType == SqlType.Text)
            {
                SqlStatemate state = new SqlStatemate(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
                reader = this.webClient.ExecuteReader(state);

            }
            else
            {
                reader = this.webClient.ExecuteStoreProcedureReader(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
            }
            ///////////////////////////////////////////////////////////////
            T entity = new T();
            if (selectFields != null && selectFields.Length > 0)
            {
                entity.SelectFields.AddRange(selectFields);
            }
            else
            {
                entity.SelectFields.AddRange(SqlUtils.GetSelectedFields(reader).ToArray());
            }
            if (reader == null)
                return entity;
            try
            {
                do
                {
                    while (reader.Read())
                    {
                        entity.MapEntity(reader);
                        break;
                    }

                } while (reader.NextResult());
            }
            finally
            {
                entity.CleanCache();
                reader.Close();
                reader.Dispose();
            }
            ////////////////////////////////////////////
            return entity;
        }

        public String ExecuteReturnString()
        {
            this.GenerateSqlParameter();
            if (this.sqlBlock.SqlType == SqlType.Text)
            {
                SqlStatemate state = new SqlStatemate(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
                return this.webClient.GetSingleResult(state);

            }
            else
            {
                return this.webClient.ExecuteStoreProcedureSingleString(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
            }
        }


        public int ExecuteReturnInt()
        {
            String valueString = ExecuteReturnString();
            if (String.IsNullOrEmpty(valueString))
                return -1;
            int intValue;
            if (int.TryParse(valueString, out intValue))
            {
                return intValue;
            }
            return -1;
        }

        public Object ExecuteReturnObject()
        {
            this.GenerateSqlParameter();
            DbDataReader reader = null;
            DbCommand returnCmd = null;
            try
            {

                if (this.sqlBlock.SqlType == SqlType.Text)
                {
                    SqlStatemate state = new SqlStatemate(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
                    reader = this.webClient.ExecuteReader(state, ref returnCmd);
                }
                else
                {
                    reader = this.webClient.ExecuteStoreProcedureReader(this.sqlBlock.Sql, ref returnCmd, this.sqlBlock.Parameters.ToArray());
                }
                if (reader == null)
                    return null;
                while (reader.Read())
                {
                    Object result = reader.GetValue(0);
                    return result;
                }
                reader.NextResult();
                return null;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                    ExtractPara(returnCmd);
                }
            }
        }

 
        public List<T> ExecuteReturnEntityList(params String[] selectFields)
        {
            this.GenerateSqlParameter();
            List<T> results = new List<T>();

            DbDataReader reader = null;
            DbCommand returnCmd = null;
            if (this.sqlBlock.SqlType == SqlType.Text)
            {
                SqlStatemate state = new SqlStatemate(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
                reader = this.webClient.ExecuteReader(state, ref returnCmd);

            }
            else
            {
                reader = this.webClient.ExecuteStoreProcedureReader(this.sqlBlock.Sql, ref returnCmd, this.sqlBlock.Parameters.ToArray());
            }
            if (reader == null)
                return results;
            String cacheKey = Guid.NewGuid().ToString();
            try
            {
                List<String> selectedFields = new List<string>();
                if (selectFields != null && selectFields.Length > 0)
                {
                    selectedFields.AddRange(selectFields);
                }
                else
                {
                    selectedFields.AddRange(SqlUtils.GetSelectedFields(reader).ToArray());
                }
                String[] selectedArray = selectedFields.ToArray();
                String[] filterArray = this.filterFields.ToArray();
                while (reader.Read())
                {
                    T entity = new T();
                    entity.CatchKey = cacheKey;
                    entity.SelectFields.AddRange(selectedArray);
                    entity.MapEntity(reader);
                    results.Add(entity);
                }
            }
            finally
            {
                reader.Close();
                reader.Dispose();
                ExtractPara(returnCmd);
                BaseEntity.CleanCache(cacheKey);
            }
            ////////////////////////////////////////////
            return results;
        }

        public Dictionary<String,T> ExecuteReturnEntityDict(params String[] selectFields)
        {
            this.GenerateSqlParameter();
            Dictionary<String, T> results = new Dictionary<String, T>();

            DbDataReader reader = null;
            DbCommand returnCmd = null;
            if (this.sqlBlock.SqlType == SqlType.Text)
            {
                SqlStatemate state = new SqlStatemate(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
                reader = this.webClient.ExecuteReader(state, ref returnCmd);

            }
            else
            {
                reader = this.webClient.ExecuteStoreProcedureReader(this.sqlBlock.Sql, ref returnCmd, this.sqlBlock.Parameters.ToArray());
            }
            if (reader == null)
                return results;
            String cacheKey = Guid.NewGuid().ToString();
            try
            {
                List<String> selectedFields = new List<string>();
                if (selectFields != null && selectFields.Length > 0)
                {
                    selectedFields.AddRange(selectFields);
                }
                else
                {
                    selectedFields.AddRange(SqlUtils.GetSelectedFields(reader).ToArray());
                }
                String[] selectedArray = selectedFields.ToArray();
                String[] filterArray = this.filterFields.ToArray();
                while (reader.Read())
                {
                    T entity = new T();
                    entity.CatchKey = cacheKey;
                    entity.SelectFields.AddRange(selectedArray);
                    entity.MapEntity(reader);
                    results[entity.ID]=entity;
                }
            }
            finally
            {
                reader.Close();
                reader.Dispose();
                ExtractPara(returnCmd);
                BaseEntity.CleanCache(cacheKey);
            }
            ////////////////////////////////////////////
            return results;
        }

        public List<String> ExecuteReturnStringList()
        {
            this.GenerateSqlParameter();
            DbDataReader reader = null;
            DbCommand returnCmd = null;
            List<String> results = new List<string>();
            if (this.sqlBlock.SqlType == SqlType.Text)
            {
                SqlStatemate state = new SqlStatemate(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
                reader = this.webClient.ExecuteReader(state, ref returnCmd);

            }
            else
            {
                reader = this.webClient.ExecuteStoreProcedureReader(this.sqlBlock.Sql, ref returnCmd, this.sqlBlock.Parameters.ToArray());
            }
            if (reader == null)
                return results;
            try
            {
                do
                {
                    while (reader.Read())
                    {
                        results.Add(reader.GetString(0));
                    }
                } while (reader.NextResult());
            }
            finally
            {
                reader.Close();
                reader.Dispose();
                ExtractPara(returnCmd);
            }
            ////////////////////////////////////////////
            return results;
        }

        public DataTable ExecuteReturnDataTable(params String[] selectFields)
        {
            this.GenerateSqlParameter();

            DataTable table = null;
            if (this.sqlBlock.SqlType == SqlType.Text)
            {
                SqlStatemate state = new SqlStatemate(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
                table = this.webClient.Execute(state);

            }
            else
            {
                table = this.webClient.ExecuteStoreProcedureReturnTable(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
            }
            return table;
        }

        public List<T> ExecutePagelyReturnEntityList(int pageNumber, int itemCount, out int totalcount, params String[] selectFields)
        {
            totalcount = 0;
            if (pageNumber < 1)
                pageNumber = 1;

            int starT = (pageNumber - 1) * itemCount;
            int endT = starT + itemCount;

            List<T> results = new List<T>();

            this.GenerateSqlParameter();
            DbDataReader reader = null;
            DbCommand returnCmd = null;
            if (this.sqlBlock.SqlType == SqlType.Text)
            {
                SqlStatemate state = new SqlStatemate(this.sqlBlock.Sql, this.sqlBlock.Parameters.ToArray());
                reader = this.webClient.ExecuteReader(state, ref returnCmd);

            }
            else
            {
                reader = this.webClient.ExecuteStoreProcedureReader(this.sqlBlock.Sql, ref returnCmd, this.sqlBlock.Parameters.ToArray());
            }
            if (reader == null)
                return results;
            String cacheKey = Guid.NewGuid().ToString();
            try
            {
                List<String> selectedFields = new List<string>();
                if (selectFields != null && selectFields.Length > 0)
                {
                    selectedFields.AddRange(selectFields);
                }
                else
                {
                    selectedFields.AddRange(SqlUtils.GetSelectedFields(reader).ToArray());
                }
                String[] selectedArray = selectedFields.ToArray();
                String[] filterArray = this.filterFields.ToArray();

                int i = -1;
                while (reader.Read())
                {
                    totalcount++;
                    i++;
                    if (i < starT || i >= endT)
                        continue;
                    T entity = new T();
                    entity.CatchKey = cacheKey;
                    entity.SelectFields.AddRange(selectedArray);
                    entity.MapEntity(reader);
                    results.Add(entity);
                }
            }
            finally
            {
                reader.Close();
                reader.Dispose();
                ExtractPara(returnCmd);
                BaseEntity.CleanCache(cacheKey);
            }
            ////////////////////////////////////////////
            return results;

        }

        ///////////////////////////////////////////////////////////////////

        private void GenerateSqlParameter()
        {
            this.ComposeSql();
            if (this.sqlBlock == null || String.IsNullOrEmpty(this.sqlBlock.Sql) || this.sqlParaDict.Count == 0)
                return;
            foreach (KeyValuePair<String, Object> sqlPara in this.sqlParaDict)
            {

                String paraName = sqlPara.Key;
                paraName = "@" + paraName.TrimStart('@');

                SqlDbType sqlType = SqlDbType.VarChar;
                if (sqlPara.Value != null)
                {
                    sqlType = SqlUtils.GetSqlDataType(sqlPara.Value.GetType(), sqlPara.Value == null ? 2 : sqlPara.Value.ToString().Length);
                }                
                SqlParameter para = SqlUtils.CreateSqlParameter(paraName, sqlType, sqlPara.Value);
                para.Direction = ParameterDirection.Input;
                this.sqlBlock.Parameters.Add(para);
            }
            foreach (KeyValuePair<String, SqlDbType> sqlOutPara in this.sqlOutParaDict)
            {
                SqlParameter para = null;
                String paraName = sqlOutPara.Key;
                paraName = "@" + paraName.TrimStart('@');
                para = new SqlParameter(paraName, sqlOutPara.Value, 500);
                para.Direction = ParameterDirection.Output;
                this.sqlBlock.Parameters.Add(para);
            }
        }

        private void ComposeSql()
        {
            if (this.sqlBlock == null || String.IsNullOrEmpty(this.sqlBlock.Sql) || this.sqlRepPara.Count == 0)
                return;
            foreach (KeyValuePair<String, String> sqlPara in this.sqlRepPara)
            {
                String repKey = sqlPara.Key.TrimStart('{').TrimEnd('}');
                this.sqlBlock.Sql = this.sqlBlock.Sql.Replace("{" + repKey + "}", sqlPara.Value);
            }
        }

        void IDisposable.Dispose()
        {
            if (this.sqlBlock != null)
                this.sqlBlock = null;
            
            this.sqlParaDict.Clear();
            this.sqlRepPara.Clear();
            this.sqlOutParaDict.Clear(); 
        }

        private void ExtractPara(DbCommand returnCmd)
        {
            if (returnCmd != null)
            {
                DbParameterCollection returnPara = returnCmd.Parameters;
                foreach (DbParameter para in returnPara)
                {
                    OutputParaDict[para.ParameterName] = para.Value;
                }
            }
        }

        public int ConvertToInt(Object value)
        {
            if (value == null)
                return 0;
            int intValue;
            int.TryParse(value.ToString(), out intValue);
            return intValue;
        }

        //////////////////////////////////////////////////////////////
        public List<T> BatchSaveEntity(params T[] entitis)
        {
            if (entitis == null || entitis.Length == 0)
                return null;

            String sql = "";
            List<T> results = new List<T>();
            for (int i = 0; i < entitis.Length; i++)
            {
                if (String.IsNullOrEmpty(sql))
                    sql = entitis[i].GetSaveSql(true,true);
                DbParameter[] parameters = entitis[i].GetEntitySqlParameter();
                SqlStatemate state = new SqlStatemate(sql, parameters);
                String newId = webClient.GetSingleResult(state);
                entitis[i].ID = newId;
                results.Add(entitis[i]);
            }
            return results;
        }

        public void BatchRemoveEntity(params T[] entitis)
        {
            if (entitis == null || entitis.Length == 0)
                return;
            String sql = "";
            for (int i = 0; i < entitis.Length; i++)
            {
                if (String.IsNullOrEmpty(sql))
                    sql = entitis[i].GetDeleteSql();
                DbParameter[] parameters = entitis[i].GetEntitySqlParameter(entitis[i].PrimaryKey);
                SqlStatemate state = new SqlStatemate(sql, parameters);
                webClient.ExecuteNoResult(state);
            }
        }
    }
}
 